package com.itheima.demo12课后作业;

import com.itheima.utils.JDBCUtils;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class Demo {

    private static DataSource dataSource = JDBCUtils.getDataSource();

    public static void main(String[] args) throws SQLException {
//        编写方法接收一个员工编号和工资两个参数，方法内将指定编号的员工工资修改为新的工资
//        updateSalaryById(1,15000);
//        编写方法查询指定职位所有员工的信息，并打印到控制台上。
//        findAllByPosition("java开发");
//        编写方法查询指定姓名的员工信息，并打印到控制台上。
        findAllByName("张三");
//        编写方法根据员工姓名删除指定的员工信息。
//        deleteById(5);
//        编写方法查询所有姓张员工的工资并输出在控制台
//        findSalaryByZhang();
//        编写方法接收一个工资参数，方法内查询工资大于等于传入的工资的员工，返回符合条件 所有员工信息 List<Employee>集合
//        findBySalary(5000.0);
//        编写方法查询指定部门的所有员工信息，返回 List<Employee>集合
//        findByDeptId("研发部");

    }

    private static void findByDeptId(String deptName) throws SQLException {
        Connection conn = dataSource.getConnection();
        String sql = "select e.*,d.name department from test_emp e left join test_dept d on e.dept_id=d.id WHERE d.name =? ";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1,deptName);
        ResultSet resultSet = ps.executeQuery();
        List<Employee> employeeList = new ArrayList<>();
        while (resultSet.next()){
            Employee employee = new Employee(
                    resultSet.getInt("id"),
                    resultSet.getString("name"),
                    resultSet.getString("sex"),
                    resultSet.getString("position"),
                    resultSet.getDouble("salary"),
                    resultSet.getDate("join_date"),
                    resultSet.getInt("dept_id")
            );
            employeeList.add(employee);

        }
        for (Employee employee : employeeList) {
            System.out.println(employee);
        }
        JDBCUtils.close(ps,conn);
    }

    private static void findBySalary(double salary) throws SQLException {
        Connection conn = dataSource.getConnection();
        String sql = "select * from test_emp where salary >= ? ";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setDouble(1,salary);
        ResultSet resultSet = ps.executeQuery();
        List<Employee> employeeList = new ArrayList<>();
        while (resultSet.next()){
            Employee employee = new Employee(
                    resultSet.getInt("id"),
                    resultSet.getString("name"),
                    resultSet.getString("sex"),
                    resultSet.getString("position"),
                    resultSet.getDouble("salary"),
                    resultSet.getDate("join_date"),
                    resultSet.getInt("dept_id")
            );
            employeeList.add(employee);

        }
        for (Employee employee : employeeList) {
            System.out.println(employee);
        }
        JDBCUtils.close(ps,conn);

    }

    private static void findSalaryByZhang() throws SQLException {
        Connection conn = dataSource.getConnection();
        String sql = "select name,salary from test_emp where name like '张%' ";
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet resultSet = ps.executeQuery();

        while (resultSet.next()){
            System.out.println(resultSet.getString("name")+"="+resultSet.getDouble("salary"));
        }

        JDBCUtils.close(ps,conn);
    }

    private static void deleteById(int id) throws SQLException {
        Connection conn = dataSource.getConnection();
        String sql ="delete from test_emp where id = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1,id);
        int i = ps.executeUpdate();
        if (i>=1){
            System.out.println("删除成功，影响行数："+i);
        }else {
            System.out.println("删除失败");
        }
        JDBCUtils.close(ps,conn);
    }

    private static void findAllByName(String name) throws SQLException {
        Connection conn = dataSource.getConnection();
        String sql = "select * from test_emp where name = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1,name);
        ResultSet resultSet = ps.executeQuery();
        List<Employee> employeeList = new ArrayList<>();
        while (resultSet.next()){
            Employee employee = new Employee(
                    resultSet.getInt("id"),
                    resultSet.getString("name"),
                    resultSet.getString("sex"),
                    resultSet.getString("position"),
                    resultSet.getDouble("salary"),
                    resultSet.getDate("join_date"),
                    resultSet.getInt("dept_id")
            );
            employeeList.add(employee);

        }
        for (Employee employee : employeeList) {
            System.out.println(employee);
        }
        JDBCUtils.close(ps,conn);
    }

    private static void findAllByPosition(String position) throws SQLException {
        Connection conn = dataSource.getConnection();
        String sql = "select * from test_emp where position = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1,position);
        ResultSet resultSet = ps.executeQuery();
        List<Employee> employeeList = new ArrayList<>();
        while (resultSet.next()){
            Employee employee = new Employee(
                    resultSet.getInt("id"),
                    resultSet.getString("name"),
                    resultSet.getString("sex"),
                    resultSet.getString("position"),
                    resultSet.getDouble("salary"),
                    resultSet.getDate("join_date"),
                    resultSet.getInt("dept_id")
            );
            employeeList.add(employee);

        }
        for (Employee employee : employeeList) {
            System.out.println(employee);
        }
    }

    private static void updateSalaryById(int id,double salary) throws SQLException {
        Connection conn = dataSource.getConnection();
        String sql = "update test_emp set salary = ? where id =?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setDouble(1,salary);
        ps.setInt(2,id);
        int i = ps.executeUpdate();
        if (i>=1){
            System.out.println("修改成功,影响行数："+i);
        }else {
            System.out.println("修改失败");
        }
        JDBCUtils.close(ps,conn);

    }

}
